﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using Ebiz.libs;
using System.Collections.Specialized;
using System.Web.Services;
using System.Web.Script.Services;
using System.Net.Mail;
using System.Net;

namespace Ebiz
{
    public partial class ViewRequest : System.Web.UI.Page
    {
        public enum EmailEngine{
            CDOSYS, NETMAIL
        }
        public enum EmailMode{
            REQUEST, APPROVED, RETURNED, REJECTED
        }

        public string IDBizCL = "";
        public string SSBizCL = "";
        public string txtxt = "";
        
        public Dictionary<string, string> ViewData = new Dictionary<string, string>();
        public Dictionary<string, string> ViewCustomer = new Dictionary<string, string>();
        public Dictionary<string, string> ViewProduct = new Dictionary<string, string>();
        protected void Page_Load(object sender, EventArgs e)
        {
            string dbName = DBUtilities.GetDBName("devCon");
            if (!IsPostBack) {
                ViewData.Add("CLNumber", "");
                ViewData.Add("CLStatus", "");
                ViewData.Add("CLButton", "");
                ViewData.Add("DistributorName", "");
                ViewData.Add("DiscountType", "");
                ViewData.Add("ChannelName", "");
                ViewData.Add("PreviousCL", "");
                ViewData.Add("CLCustomerName", "");
                ViewData.Add("CustomerGroup", "");
                ViewCustomer.Add("CustomerList", "");
                ViewCustomer.Add("TotalCustomer", "");
                ViewData.Add("DiscountReason", "");
                ViewData.Add("DateStart", "");
                ViewData.Add("DateEnd", "");
                ViewData.Add("ProductDivision", "");
                ViewProduct.Add("ProductList", "");
                ViewProduct.Add("TotalProduct", "");
                ViewData.Add("FileList", "");
                ViewData.Add("ApprovalList", "");
                ViewData.Add("CLDownload", "");
                ViewData.Add("ApvButton", "");

                if (Request.QueryString["cl"] != null && 
                    !(string.IsNullOrEmpty(Request.QueryString["cl"]))){

                    string DecryptQry = CryptLib.Base64(Request.QueryString["cl"], CryptMode.DECRYPT);
                    NameValueCollection ResX = HttpUtility.ParseQueryString(DecryptQry);
                    int ResXLen = ResX.Count;
                    if (ResXLen >= 2) {
                        string DecryptCLNum = ResX[0].ToString();
                        string DecryptToken = ResX[1].ToString();
                        if (string.IsNullOrEmpty(DecryptCLNum) || string.IsNullOrEmpty(DecryptToken)) {
                            Session["ErrCode"] = "ES-404";
                            Response.Redirect("ErrorPage.aspx", true);
                        }else{
                            bool isThisPage = true;

                            //string DecryptQry = CryptLib.Base64(Request.QueryString["cl"], CryptMode.DECRYPT);
                            Dictionary<string, object> isRequestExist = CheckRequest(DecryptCLNum, DecryptToken);
                            string statCheck = isRequestExist["StatusCode"].ToString();
                            if ((int)isRequestExist["Exist"] == 1){
                                switch (statCheck) { 
                                    case "200":
                                    case "201":
                                    case "203":
                                    case "204":
                                        isThisPage = true;
                                        break;
                                    case "210":
                                    case "211":
                                    case "213":
                                    case "214":
                                        isThisPage = false;
                                        break;
                                }
                                //LoadRequest(DecryptCLNum, DecryptToken);
                                if(isThisPage == true){
                                    Dictionary<string, object> tmpData = LoadRequestData(DecryptCLNum, DecryptToken);
                                    //txtxt = tmpData["qry"].ToString();
                                    ViewData["CLNumber"] = tmpData["CLNumber"].ToString();
                                    ViewData["CLStatus"] = tmpData["CLStatus"].ToString();
                                    ViewData["PreviousCL"] = tmpData["PreviousCL"].ToString();
                                    ViewData["DistributorName"] = tmpData["DistributorName"].ToString();
                                    ViewData["CLButton"] = tmpData["CLButton"].ToString();
                                    ViewData["DiscountType"] = tmpData["DiscountType"].ToString();
                                    ViewData["ChannelName"] = tmpData["ChannelName"].ToString();
                                    ViewData["CLCustomerName"] = tmpData["CLCustomerName"].ToString();
                                    ViewData["CustomerGroup"] = tmpData["CustomerGroup"].ToString();
                                    ViewData["DiscountReason"] = tmpData["DiscountReason"].ToString();
                                    ViewData["ProductDivision"] = tmpData["ProductDivision"].ToString();
                                    ViewData["DateStart"] = tmpData["DateStart"].ToString();
                                    ViewData["DateEnd"] = tmpData["DateEnd"].ToString();
                                    ViewData["FileList"] = tmpData["FileList"].ToString();
                                    ViewData["ApprovalList"] = tmpData["ApprovalList"].ToString();
                                    ViewCustomer["CustomerList"] = tmpData["CustomerList"].ToString();
                                    ViewCustomer["TotalCustomer"] = tmpData["TotalCustomer"].ToString();
                                    ViewProduct["ProductList"] = tmpData["ProductList"].ToString();
                                    ViewProduct["TotalProduct"] = tmpData["TotalProduct"].ToString();
                                    ViewData["RequesterID"] = tmpData["RequesterID"].ToString();

                                    string _ApvButton = "";
                                    string _DownloadBtn = "";
                                    string _EditButton = "";
                                    if (Session["UserInfo"] == null) {
                                        if (ResXLen == 4) {
                                            bool isAuto = Convert.ToBoolean(ResX[3].ToString());
                                            if (isAuto == true) {
                                                string ViewerID = ResX[2].ToString();
                                                string qryViewer = "SELECT UserAccounts.UserName, UserAccounts.PCName, " +
                                                "UserAccounts.IDNetwork, UserAccounts.FullName, UserAccounts.Email, " +
                                                "UserAccounts.IDRole, mstRole.RoleAlias, mstRole.IDMenu " +
                                                "FROM " + dbName + ".dbo.UserAccounts " +
                                                "INNER JOIN " + dbName + ".dbo.mstRole ON UserAccounts.IDRole = mstRole.IDRole " +
                                                "WHERE (UserAccounts.UserName = @UserName)";
                                                SqlConnection cnViewer = new SqlConnection(DBUtilities.strConnection("devCon"));
                                                using (cnViewer){
                                                    try{
                                                        cnViewer.Open();

                                                        SqlCommand cmdViewer = new SqlCommand(qryViewer, cnViewer);
                                                        cmdViewer.Parameters.AddWithValue("@UserName", ViewerID);
                                                        SqlDataReader rdrViewer = cmdViewer.ExecuteReader();
                                                        if (rdrViewer.HasRows){
                                                            if (rdrViewer.Read()){
                                                                //_ApvButton = ApprovalButton(tmpData["StatusCode"].ToString(), tmpData["ApprovalCurrent"].ToString(), rdrViewer.GetValue(0).ToString());
                                                                _ApvButton = ApprovalButton(tmpData["StatusCode"].ToString(), tmpData["ApprovalCurrent"].ToString(), ViewerID);
                                                                _EditButton = EditCL(tmpData["StatusCode"].ToString(), tmpData["RequesterID"].ToString(), ViewerID);
                                                                ViewData["IDViewer"] = rdrViewer.GetValue(0).ToString();
                                                                _DownloadBtn = SetCLDownload(tmpData["StatusCode"].ToString(), rdrViewer.GetValue(0).ToString());
                                                            }else{
                                                                Session["ErrCode"] = "ES-104";
                                                                Response.Redirect("ErrorPage.aspx", true);
                                                            }
                                                        }else{
                                                            Session["ErrCode"] = "ES-104";
                                                            Response.Redirect("ErrorPage.aspx", true);
                                                        }
                                                    }catch (Exception ex){
                                                        string errMsg = ex.Message;
                                                    }finally{
                                                        if (cnViewer.State == ConnectionState.Open){
                                                            cnViewer.Close();
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }else{
                                        _ApvButton = ApprovalButton(tmpData["StatusCode"].ToString(), tmpData["ApprovalCurrent"].ToString());
                                        ViewData["IDViewer"] = Func.GetUserInfo(Func.ShowInfo._UserID);
                                        _DownloadBtn = SetCLDownload(tmpData["StatusCode"].ToString());
                                        _EditButton = EditCL(tmpData["StatusCode"].ToString(), tmpData["RequesterID"].ToString());
                                    }
                                    ViewData["ApvButton"] = _ApvButton;
                                    ViewData["CLDownload"] = _DownloadBtn;
                                    ViewData["EditButton"] = _EditButton;

                                }else{
                                    string goToBulk = "BulkView.aspx?cl=" + Request.QueryString["cl"];
                                    Response.Redirect(goToBulk, true);
                                }
                            }else{
                                Session["ErrCode"] = "ES-404";
                                Response.Redirect("ErrorPage.aspx", true);
                            }
                        }
                    }
                }else{
                    Session["ErrCode"] = "ES-404";
                    Response.Redirect("ErrorPage.aspx", true);
                }
            }
        }

        private static Dictionary<string,object> CheckRequest(string CLNumber, string SSID) {
            Dictionary<string,object> result = new Dictionary<string,object>();

            int isExist = 0;
            string StatusCode = "";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string dbName = DBUtilities.GetDBName("devCon");
            using (cn) {
                string qry = "SELECT CLNum, StatusCode FROM " + 
                    dbName + ".dbo.tbl_Request WHERE " +
                    "(CLNum = @CLNum) AND (Token = @Token)";
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    cmd.Parameters.AddWithValue("@Token", SSID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()) {
                            isExist = 1;
                            StatusCode = rdr.GetValue(1).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            result.Add("Exist", isExist);
            result.Add("StatusCode", StatusCode);

            return result;
        }

        private static Dictionary<string, object> LoadRequestData(string CLNumber, string SSID) {
            Dictionary<string, object> result = new Dictionary<string, object>();

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_Request.CLNum, tbl_Request.PrevCL, tbl_Request.DistributorCode, " +
                "tbl_Request.CLCustName, tbl_Request.CustomerGroup, tbl_Request.Channel, " +
                "tbl_Request.DateRequest, " + 
                "CONVERT(varchar, tbl_Request.DateStart, 105) AS DateStart, " +
                "CONVERT(varchar, tbl_Request.DateEnd, 105) AS DateEnd, " +
                "tbl_Request.DateClosed, tbl_Request.DiscType, tbl_Request.ProdDivision, " +
                "tbl_Request.DiscReason, tbl_Request.ApprovalCurrent, tbl_Request.StatusCode, " +
                "mstDistributor.DistributorName AS DSTName, " +
                "mstChannel.ChannelName AS CHName, tbl_Request.CreatedBy " +
                "FROM " + dbName + ".dbo.tbl_Request INNER JOIN " +
                dbName + ".dbo.mstDistributor ON tbl_Request.DistributorCode = mstDistributor.DistributorCode " +
                "INNER JOIN " + dbName + ".dbo.mstChannel ON tbl_Request.Channel = mstChannel.IDChannel WHERE " +
                //"(tbl_Request.CLNum = '" + CLNumber + "') AND (tbl_Request.Token = '" + SSID + "')";
                "(tbl_Request.CLNum = @CLNum) AND (tbl_Request.Token = @Token)";

            //result.Add("qry", qry);
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    cmd.Parameters.AddWithValue("@Token", SSID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            //result.Add("qry", qry);

                            string _CLNum = rdr.GetValue(0).ToString();
                            string _StatusCode = rdr.GetValue(14).ToString();
                            string _ApprovalCurrent = rdr.GetValue(13).ToString();
                            string _CLStatus = GetCLStatus(_StatusCode, _ApprovalCurrent);
                            string _PrevCL = rdr.GetValue(1).ToString();
                            string _DistributorCode = rdr.GetValue(2).ToString();
                            string _DistributorName = rdr.GetValue(15).ToString();
                            string _ActionButton = "<a id=\"btnView\" class=\"btnAction btnSmall\" " +
                                "title=\"View Approval List\" href=\"#apvProc\">Approval List</a>";

                            string _DiscType = rdr.GetValue(10).ToString();
                            string _CLCustName = rdr.GetValue(3).ToString();
                            string _CustomerGroup = rdr.GetValue(4).ToString();
                            string _ChannelCode = rdr.GetValue(5).ToString();
                            string _ChannelName = rdr.GetValue(16).ToString();
                            string _DiscReason = rdr.GetValue(12).ToString();
                            string _DateRequest = rdr.GetValue(6).ToString();
                            string _ProdDivision = rdr.GetValue(11).ToString();
                            string _DateStart = rdr.GetValue(7).ToString();
                            string _DateEnd = rdr.GetValue(8).ToString();
                            string _DateClosed = rdr.GetValue(9).ToString();
                            //string _CLDownload = SetCLDownload(_StatusCode);
                            string _CreatedBy = rdr.GetValue(17).ToString();
                            
                            result.Add("CLNumber", _CLNum);
                            result.Add("StatusCode", _StatusCode);
                            result.Add("CLStatus", _CLStatus);
                            result.Add("ApprovalCurrent", _ApprovalCurrent);
                            result.Add("PreviousCL", _PrevCL);
                            result.Add("Distributor", _DistributorCode);
                            result.Add("DistributorName", _DistributorName);

                            result.Add("DiscountType", _DiscType);
                            result.Add("CLCustomerName", _CLCustName);
                            result.Add("CustomerGroup", _CustomerGroup);
                            result.Add("Channel", _ChannelCode);
                            result.Add("ChannelName", _ChannelName);
                            result.Add("DiscountReason", _DiscReason);
                            result.Add("ProductDivision", _ProdDivision);
                            result.Add("DateRequest", _DateRequest);
                            result.Add("DateStart", _DateStart);
                            result.Add("DateEnd", _DateEnd);
                            result.Add("DateClosed", _DateClosed);
                            result.Add("FileList", DocumentList(_CLNum));
                            result.Add("ApprovalList", ApprovalList(_CLNum));

                            int TotalCust = TotalCustomer(CLNumber);
                            result.Add("TotalCustomer", TotalCust.ToString());
                            result.Add("CustomerList", TableCustomer(_CLNum, TotalCust));

                            int TotalProd = TotalProduct(CLNumber);
                            result.Add("TotalProduct", TotalProd.ToString());
                            result.Add("ProductList", TableProduct(_CLNum, TotalProd));

                            result.Add("CLButton", _ActionButton);
                            //result.Add("CLDownload", _CLDownload);
                            result.Add("RequesterID", _CreatedBy);
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private void LoadRequest(string CLNumber, string SSID) {
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            string dbName = DBUtilities.GetDBName("devCon");
            using (cn) {
                string qry = "SELECT tbl_Request.CLNum, tbl_Request.PrevCL, tbl_Request.DistributorCode, " +
                    "tbl_Request.CLCustName, tbl_Request.CustomerGroup, tbl_Request.Channel, " +
                    "tbl_Request.DateRequest, tbl_Request.DateStart, tbl_Request.DateEnd, " +
                    "tbl_Request.DateClosed, tbl_Request.DiscType, tbl_Request.ProdDivision, " +
                    "tbl_Request.DiscReason, tbl_Request.ApprovalCurrent, tbl_Request.StatusCode, " +
                    "mstDistributor.DistributorName AS DSTName, " +
                    "mstChannel.ChannelName AS CHName " +
                    "FROM " + dbName + ".dbo.tbl_Request INNER JOIN " +
                    dbName + ".dbo.mstDistributor ON tbl_Request.DistributorCode = mstDistributor.DistributorCode " +
                    "INNER JOIN " + dbName + ".dbo.mstChannel ON tbl_Request.Channel = mstChannel.IDChannel WHERE " + 
                    "(CLNum = @CLNum) AND (Token = @Token)";
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    cmd.Parameters.AddWithValue("@Token", SSID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            string _CLNum = rdr.GetValue(0).ToString();
                            string _PrevCL = rdr.GetValue(1).ToString();
                            string _DistributorCode = rdr.GetValue(2).ToString();
                            string _CLCustName = rdr.GetValue(3).ToString();
                            string _CustomerGroup = rdr.GetValue(4).ToString();
                            string _ChannelCode = rdr.GetValue(5).ToString();
                            string _DateRequest = rdr.GetValue(6).ToString();
                            string _DateStart = rdr.GetValue(7).ToString();
                            string _DateEnd = rdr.GetValue(8).ToString();
                            string _DateClosed = rdr.GetValue(9).ToString();
                            string _DiscType = rdr.GetValue(10).ToString();
                            string _ProdDivision = rdr.GetValue(11).ToString();
                            string _DiscReason = rdr.GetValue(12).ToString();
                            string _ApprovalCurrent = rdr.GetValue(13).ToString();
                            string _StatusCode = rdr.GetValue(14).ToString();
                            string _DistributorName = rdr.GetValue(15).ToString();
                            string _ChannelName = rdr.GetValue(16).ToString();

                            string _CLStatus = GetCLStatus(_StatusCode, _ApprovalCurrent);
                            string _ActionButton = ActionButton(_StatusCode, _ApprovalCurrent);
                            string _ApvButton = ApprovalButton(_StatusCode, _ApprovalCurrent);
                            string _CLDownload = SetCLDownload(_StatusCode);

                            string[] _tmpDateStart = (_DateStart).Split(' ');
                            string tmpDateStart = _tmpDateStart[0];
                            string DateStart = Func.FormatDate(tmpDateStart, Func.DTFormat.MDYtoDMY, "/", "-");

                            string[] _tmpDateEnd = (_DateEnd).Split(' ');
                            string tmpDateEnd = _tmpDateEnd[0];
                            string DateEnd = Func.FormatDate(tmpDateEnd, Func.DTFormat.MDYtoDMY, "/", "-");

                            //ViewData.Add("CLNumber", _CLNum);
                            //ViewData.Add("PreviousCL", _PrevCL);
                            ViewData.Add("Distributor", _DistributorCode);
                            //ViewData.Add("CLCustomerName", _CLCustName);
                            //ViewData.Add("CustomerGroup", _CustomerGroup);
                            ViewData.Add("Channel", _ChannelCode);
                            ViewData.Add("DateRequest", _DateRequest);
                            //ViewData.Add("DateStart", DateStart);
                            //ViewData.Add("DateEnd", DateEnd);
                            ViewData.Add("DateClosed", _DateClosed);
                            //ViewData.Add("DiscountType", _DiscType);
                            //ViewData.Add("ProductDivision", _ProdDivision);
                            //ViewData.Add("DiscountReason", _DiscReason);
                            ViewData.Add("ApprovalCurrent", _ApprovalCurrent);
                            //ViewData.Add("CLStatus", _CLStatus);
                            //ViewData.Add("DistributorName", _DistributorName);
                            //ViewData.Add("ChannelName", _ChannelName);
                            //ViewData.Add("CLDownload", _CLDownload);
                            //ViewData.Add("CLButton", _ActionButton);
                            //ViewData.Add("ApvButton", _ApvButton);
                            //ViewData.Add("FileList", DocumentList(_CLNum));
                            //ViewData.Add("ApprovalList", ApprovalList(_CLNum));

                            ViewData["CLNumber"] = _CLNum;
                            ViewData["CLStatus"] = _CLStatus;
                            ViewData["CLButton"] = _ActionButton;
                            ViewData["DistributorName"] = _DistributorName;
                            ViewData["DiscountType"] = _DiscType;
                            ViewData["ChannelName"] = _ChannelName;
                            ViewData["PreviousCL"] = _PrevCL;
                            ViewData["CLCustomerName"] = _CLCustName;
                            ViewData["CustomerGroup"] = _CustomerGroup;
                            ViewData["DiscountReason"] = _DiscReason;
                            ViewData["DateStart"] = DateStart;
                            ViewData["DateEnd"] = DateEnd;
                            ViewData["ProductDivision"] = _ProdDivision;
                            ViewData["FileList"] = DocumentList(_CLNum);
                            ViewData["ApprovalList"] = ApprovalList(_CLNum);
                            ViewData["CLDownload"] = _CLDownload;
                            ViewData["ApvButton"] = _ApvButton;

                            int TotalCust = TotalCustomer(CLNumber);
                            //ViewCustomer.Add("TotalCustomer", TotalCust.ToString());
                            //ViewCustomer.Add("CustomerList", TableCustomer(_CLNum, TotalCust));
                            ViewCustomer["CustomerList"] = TableCustomer(_CLNum, TotalCust);
                            ViewCustomer["TotalCustomer"] = TotalCust.ToString();

                            int TotalProd = TotalProduct(CLNumber);
                            //ViewProduct.Add("TotalProduct", TotalProd.ToString());
                            //ViewProduct.Add("ProductList", TableProduct(_CLNum, TotalProd));
                            ViewProduct["ProductList"] = TableProduct(_CLNum, TotalProd);
                            ViewProduct["TotalProduct"] = TotalProd.ToString();

                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
        }

        private static string GetCLStatus(string StatusCode, string UserID) {
            string result = "";

            Dictionary<string, string> tmpResult = Func.GetStatusInfo(StatusCode);
            switch (tmpResult["CODE"]) { 
                case "100": case "101":
                    result = "On Progress";
                    break;
                case "200":
                    string ApvName = GetFullName(UserID);
                    result = tmpResult["DESCRIPTION"] + " by " + ApvName;
                    break;
                default:
                    result = tmpResult["DESCRIPTION"];
                    break;
            }
            return result;
        }

        private static string GetFullName(string UserID){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT FullName FROM " + dbName + ".dbo.UserAccounts WHERE " +
                "(UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", UserID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string SetCLDownload(string StatusCode) {
            string result = "";

            bool isExist = false;
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT DISTINCT(tbl_ApprovalStep.CurrentStep) AS Requester " +
                "FROM " + dbName + ".dbo.tbl_ApprovalStep " +
                "WHERE tbl_ApprovalStep.StepSeq = 1";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        List<string> lstActor = new List<string>();
                        while (rdr.Read()){
                            lstActor.Add(rdr.GetValue(0).ToString());
                        }
                        string ActGroup = Func.GetUserInfo(Func.ShowInfo._RoleID);
                        foreach (string str in lstActor) {
                            if (str.Contains(ActGroup)) {
                                isExist = true;
                                break;
                            }
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }

            if (StatusCode == "201" && isExist == true) {
                result = "<a id='btnCLDown' class='btnForm' style='width:120px;margin-left:10px;' " +
                "title='Download CL' href=\"#\">Download CL</a>'";
            }
            
            return result;
        }

        private static string EditCL(string StatusCode, string RequesterID) {
            string result = "";
            string IDViewer = Func.GetUserInfo(Func.ShowInfo._UserID);
            if(StatusCode == "203" && (RequesterID == IDViewer)){
                result = "<a id='btnEditCL' class='btnForm' style='margin-left:10px;' " +
                "title='Edit CL' href=\"#\">Edit CL</a>'";
            }
            return result;
        }

        private static string EditCL(string StatusCode, string RequesterID, string IDViewer) {
            string result = "";
            if (StatusCode == "203" && (RequesterID == IDViewer)){
                result = "<a id='btnEditCL' class='btnForm' style='margin-left:10px;' " +
                "title='Edit CL' href=\"#\">Edit CL</a>'";
            }
            return result;
        }

        private static string SetCLDownload(string StatusCode, string IDViewer){
            string result = "";

            bool isExist = false;
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT DISTINCT(tbl_ApprovalStep.CurrentStep) AS Requester " +
                "FROM " + dbName + ".dbo.tbl_ApprovalStep " +
                "WHERE tbl_ApprovalStep.StepSeq = 1";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        List<string> lstActor = new List<string>();
                        while (rdr.Read()){
                            lstActor.Add(rdr.GetValue(0).ToString());
                        }
                        string ActGroup = IDViewer;
                        foreach (string str in lstActor){
                            if (str.Contains(ActGroup)){
                                isExist = true;
                                break;
                            }
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }

            if (StatusCode == "201" && isExist == true){
                result = "<a id='btnCLDown' class='btnForm' style='width:120px;margin-left:10px;' " +
                "title='Download CL' href=\"#\">Download CL</a>'";
            }

            return result;
        }

        private static string ApprovalButton(string StatusCode, string ApvCurrent) {
            string result = "";
            
            string LoggedPerson = Func.GetUserInfo(Func.ShowInfo._UserID);
            if (ApvCurrent == LoggedPerson && StatusCode == "200"){
                //result = "ok";
                result = "<a id='btnApprove' class='btnAction btnForm' style='width:120px;margin-left:10px;' " +
                "title='Set Approval' href=\"#\">Set Approval</a>'";
            }
            return result;
        }

        private static string ApprovalButton(string StatusCode, string ApvCurrent, string LoggedPerson){
            string result = "";
            if (ApvCurrent == LoggedPerson && StatusCode == "200"){
                result = "<a id='btnApprove' class='btnAction btnForm' style='width:120px;margin-left:10px;' " +
                "title='Set Approval' href=\"#\">Set Approval</a>'";
            }
            return result;
        }

        private static string ActionButton(string StatusCode, string ApvCurrent) {
            string result = "";

            //string LoggedPerson = Func.GetUserInfo(Func.ShowInfo._UserID);
            result = "<a id='btnView' class='btnAction btnSmall' " +
                "title='View Approval List' href=\"#apvProc\">Approval List</a>'";
            return result;
        }

        private static string TableCustomer(string CLNumber, int TotalCust){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT CustCode, CSCodeAZI, CustName, CustAddress FROM " + 
                dbName + ".dbo.tbl_RequestCustomer WHERE (CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        int idx = 0;

                        string TRow = "<tr ";
                        string TROpt = "    <td style=\"width:24px;\"></td>\n";
                        string TREnd = "</tr>\n";
                        string TCell = "    <td ";
                        string Cel12 = "style=\"width:85px;\">";
                        string Cell3 = "style=\"width:267px;\">";
                        string Cell4 = "style=\"width:258px;\">";
                        string TDCode = TCell + Cel12;
                        string TDName = TCell + Cell3;
                        string TDAddr = TCell + Cell4;
                        string TDEnd = "</td>\n";
                        while (rdr.Read()) {
                            idx++;

                            string SetBorder = (idx == TotalCust) ? " style=\"border-bottom:1px solid #CCC;\"" : "";
                            string rowClass = ((idx % 2) == 1) ? "class=\"odd\"" + SetBorder : "class=\"even\"" + SetBorder;
                            string TRStart = TRow + rowClass + ">\n";

                            string _CustCode = rdr.GetValue(0).ToString();
                            string _CSCodeAZI = rdr.GetValue(1).ToString();
                            string _CustName = rdr.GetValue(2).ToString();
                            string _tmpCustAddress = rdr.GetValue(3).ToString();
                            string _CustAddress = (string.IsNullOrEmpty(_tmpCustAddress)) ? "" : _tmpCustAddress;

                            string tmpResult = TRStart + TDCode + _CustCode + TDEnd;
                            tmpResult += TDCode + _CSCodeAZI + TDEnd;
                            tmpResult += TDName + _CustName + TDEnd;
                            tmpResult += TDAddr + _CustAddress + TDEnd;
                            tmpResult += TROpt + TREnd;

                            result += tmpResult;
                        }
                    }
                }catch (Exception ex) {
                    string errMsg = ex.Message;
                }
                finally {
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int TotalCustomer(string CLNumber) {
            int result = 0;
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_RequestCustomer WHERE " + 
                "(CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()) {
                            result = Convert.ToInt32(rdr.GetValue(0));
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string TableProduct(string CLNumber, int TotalProd) {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT ProductCode, ProductName, Discount, Quantity FROM " +
                dbName + ".dbo.tbl_RequestProduct WHERE (CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        int idx = 0;

                        string TRow = "<tr ";
                        string TROpt = "    <td style=\"width:24px;\"></td>\n";
                        string TREnd = "</tr>\n";
                        string TCell = "    <td ";
                        string Cell_12 = "style=\"width:85px;\">";
                        string Cell_3 = "style=\"width:458px;\">";
                        string Cell_45 = "width:76px;\">";
                        string TDCode = TCell + Cell_12;
                        string TDName = TCell + Cell_3;
                        string TDDQty = "<td style=\"text-align:right;" + Cell_45;
                        string TDEnd = "</td>\n";
                        while (rdr.Read()){
                            idx++;

                            string SetBorder = (idx == TotalProd) ? " style=\"border-bottom:1px solid #CCC;\"" : "";
                            string rowClass = ((idx % 2) == 1) ? "class=\"odd\"" + SetBorder : "class=\"even\"" + SetBorder;
                            string TRStart = TRow + rowClass + ">\n";

                            string _ProdCode = rdr.GetValue(0).ToString();
                            string _ProdName = rdr.GetValue(1).ToString();
                            string _Disc = rdr.GetValue(2).ToString();
                            string _Qty = rdr.GetValue(3).ToString();

                            string tmpResult = TRStart + TDCode + _ProdCode + TDEnd;
                            tmpResult += TDName + _ProdName + TDEnd;
                            tmpResult += TDDQty + _Disc + TDEnd;
                            tmpResult += TDDQty + _Qty + TDEnd;
                            tmpResult += TROpt + TREnd;

                            result += tmpResult;
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int TotalProduct(string CLNumber){
            int result = 0;
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT COUNT(CLNum) FROM " + dbName + ".dbo.tbl_RequestProduct WHERE " +
                "(CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = Convert.ToInt32(rdr.GetValue(0));
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string DocumentList(string CLNumber) {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT FileLocation, FileName FROM " + dbName + ".dbo.tbl_RequestFiles WHERE " +
                "(CLNum = @CLNum) AND (StatusCode = '101')";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        result = "<ul id=\"olDoc\">\n";
                        while (rdr.Read()) {
                            string FolderLoc = rdr.GetValue(0).ToString();
                            string FileName = rdr.GetValue(1).ToString();
                            string FileLink = FolderLoc + FileName;
                            string Target = "target=\"_blank\"";
                            string ALink = "<a href=\"" + FileLink + "\" " + Target + ">";
                            ALink += FileName + "</a>";

                            string tmpResult = "<li>" + ALink + "</li>\n";

                            result += tmpResult;
                        }
                        result += "</ul>\n";
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string ApprovalList(string CLNumber){
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT CLNum, CurrentStep, " + dbName + ".dbo.UserAccounts.FullName, Notes, " + 
                dbName + ".dbo.tbl_RequestApproval.StatusCode " +
                "FROM " + dbName + ".dbo.tbl_RequestApproval INNER JOIN " + dbName +
                ".dbo.UserAccounts ON CurrentStep = " + dbName + ".dbo.UserAccounts.UserName WHERE " +
                "(CLNum = @CLNum)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNumber);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        result = "<ol id=\"olApv\">\n";
                        while (rdr.Read()){
                            string ApvCode = rdr.GetValue(1).ToString();
                            string ApvName = rdr.GetValue(2).ToString();
                            string _Notes = rdr.GetValue(3).ToString();
                            string _Status = rdr.GetValue(4).ToString();
                            string Notes = (string.IsNullOrEmpty(_Notes)) ? "" : "<span class=\"spnNote\" title=\"View Notes\">View Notes</span>";
                            string StatusCode = "";
                            switch (_Status) { 
                                case "200":
                                    StatusCode = "<span class=\"spnStatus\">Status: Waiting for Approval</span>";
                                    break;
                                case "201":
                                    StatusCode = "<span class=\"spnStatus\">Status: Approved</span>";
                                    break;
                                case "203":
                                    StatusCode = "<span class=\"spnStatus\">Status: Returned for Information</span>";
                                    break;
                                case "204":
                                    StatusCode = "<span class=\"spnStatus\">Status: Rejected</span>";
                                    break;
                            }
                            string tmpResult = "<li><input type=\"hidden\" value=\"" + ApvCode + "\" />" + 
                                ApvName + StatusCode + Notes + "</li>\n";

                            result += tmpResult;
                        }
                        result += "</ol>\n";
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> ViewNotes(string IDReq, string IDApv)
        {
            List<object> result = new List<object>();
            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT CLNum, CurrentStep, Notes, " + dbName + ".dbo.UserAccounts.FullName " +
                "FROM " + dbName + ".dbo.tbl_RequestApproval INNER JOIN " + dbName +
                ".dbo.UserAccounts ON CurrentStep = " + dbName + ".dbo.UserAccounts.UserName WHERE " +
                "(CLNum = @CLNum) AND (CurrentStep = @CurrentStep)";

            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", IDReq);
                    cmd.Parameters.AddWithValue("@CurrentStep", IDApv);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            string Notes = rdr.GetValue(2).ToString();
                            string ApvName = rdr.GetValue(3).ToString();
                            result.Add(1);
                            result.Add(ApvName);
                            result.Add(Notes);
                        }else{
                            result.Add(0);
                            result.Add("No data found!");
                            result.Add("");
                        }
                    }else{
                        result.Add(0);
                        result.Add("No data found!");
                        result.Add("");
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                    result.Add(0);
                    result.Add(errMsg);
                    result.Add("");
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        [WebMethod]
        [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
        public static List<object> SetApproval(string IDCL, string ApvStatus, string ApvNotes, 
            string IDViewer) {
            
            List<object> result = new List<object>();
            
            List<object> lstCLData = CLData(IDCL);
            int StepSeq = (int)lstCLData[1];
            int StepSeqNext = StepSeq + 1;
            string ApvCurrentID = lstCLData[2].ToString();
            //string ApvNextID = (string.IsNullOrEmpty(lstCLData[3].ToString())) ? "0" : lstCLData[3].ToString();
            string ApvNextID = (string.IsNullOrEmpty(lstCLData[3].ToString())) ? "0" : lstCLData[3].ToString();
            string ApvNextName = lstCLData[4].ToString();
            string ApvNextEmail = lstCLData[5].ToString();
            string RequesterID = lstCLData[6].ToString();
            string RequesterName = lstCLData[7].ToString();
            string RequesterEmail = lstCLData[8].ToString();
            string Token = lstCLData[9].ToString();
            //string dtToday = DateTime.Today.ToString("yyyy/MM/dd hh:mm:ss");
            string NextPersonID = GetApvNextID(IDCL, StepSeqNext, ApvNextID);

            //string EmailSubject = "BizCase request with CL Number " + IDCL;
            string ESubject = "BizCase request with CL Number " + IDCL;
            Dictionary<string, string> dctEmail = new Dictionary<string, string>();
            dctEmail.Add("CLNum", IDCL);
            dctEmail.Add("Subject", ESubject);
            dctEmail.Add("Link", generatePageLink(IDCL, Token));
            dctEmail.Add("AppvPersonExt", "");
            dctEmail.Add("RequesterName", RequesterName);
            dctEmail.Add("MailCC", RequesterEmail);
            
            Dictionary<string, string> dctEmailSub = new Dictionary<string, string>();
            dctEmailSub.Add("CLNum", IDCL);
            dctEmailSub.Add("Subject", ESubject);
            dctEmailSub.Add("Link", generatePageLink(IDCL, Token));
            dctEmailSub.Add("AppvPersonExt", "");
            dctEmailSub.Add("RequesterName", RequesterName);
            dctEmailSub.Add("MailCC", RequesterEmail);

            string dbName = DBUtilities.GetDBName("devCon");
            if ((bool)lstCLData[0] == true) { //IF data exist
                if (ApvStatus == "1"){
                    //If Appv Step is Completed, send email only to requester
                    if (string.IsNullOrEmpty(ApvNextID) || ApvNextID == "0"){

                        string QryMain = "UPDATE " + dbName + ".dbo.tbl_Request SET " + 
                            "tbl_Request.ModifiedBy = @ModifiedBy, " + 
                            "tbl_Request.ModifiedDate = GetDate(), " + 
                            "tbl_Request.StatusCode = '201' WHERE " +
                            "(tbl_Request.CLNum = @CLNum)";
                        Dictionary<string, object> dctQryMain = new Dictionary<string, object>();
                        dctQryMain.Add("@ModifiedBy", ApvCurrentID);
                        dctQryMain.Add("@CLNum", IDCL);

                        if (ExecQuery(QryMain, dctQryMain) > 0) {

                            string QrySub = "UPDATE " + dbName + ".dbo.tbl_RequestApproval SET " + 
                                "tbl_RequestApproval.Notes = @Notes, " +
                                "tbl_RequestApproval.StatusCode = '201' WHERE " +
                                "(tbl_RequestApproval.CLNum = @CLNum) AND " +
                                "(tbl_RequestApproval.StepSeq = @StepSeq)";
                            Dictionary<string, object> dctQrySub = new Dictionary<string, object>();
                            dctQrySub.Add("@Notes", ApvNotes);
                            dctQrySub.Add("@CLNum", IDCL);
                            dctQrySub.Add("@StepSeq", StepSeq);
                            
                            if (ExecQuery(QrySub, dctQrySub) > 0) {
                                //Send Email: Approved Mode
                                dctEmail.Add("MailTo", RequesterEmail);
                                dctEmail.Add("ApvName", "");

                                Dictionary<string,object> EmailStatus = SendEmail(dctEmail, EmailMode.APPROVED, EmailEngine.NETMAIL);
                                int EmailSentStatus = Convert.ToInt32(EmailStatus["Status"].ToString());
                                if (Convert.ToInt32(EmailStatus["Status"].ToString()) > 0) {
                                    //TODO: Update DB Details. Email Sent = 1

                                    //Send Message Success
                                    result.Add(1);
                                    result.Add("Thank you.\nYour approval has been successfully updated");
                                }else{
                                    //If Failed RollBack All DBs
                                    string qryRollBack = "UPDATE " + dbName + ".dbo.tbl_Request SET " +
                                    "tbl_Request.StatusCode = '200' WHERE " +
                                    "(tbl_Request.CLNum = @CLNum)";
                                    Dictionary<string, object> dctRollBack = new Dictionary<string, object>();
                                    dctRollBack.Add("@CLNum", IDCL);

                                    if (ExecQuery(qryRollBack, dctRollBack) > 0){
                                        string qryRollBackExt = "UPDATE " + dbName + ".dbo.tbl_RequestApproval SET" +
                                            "tbl_RequestApproval.StatusCode = '200' WHERE " +
                                            "(tbl_RequestApproval.CLNum = @CLNum) AND " +
                                            "(tbl_RequestApproval.StepSeq = @StepSeq)";
                                        Dictionary<string, object> dctRollBackExt = new Dictionary<string, object>();
                                        dctRollBackExt.Add("@CLNum", IDCL);
                                        dctRollBackExt.Add("@StepSeq", StepSeq);
                                        if (ExecQuery(qryRollBackExt, dctRollBackExt) > 0){
                                            //Send Message Failure to user
                                            result.Add(0);
                                            result.Add(EmailStatus["Message"].ToString());
                                        }else{
                                            result.Add(0);
                                            result.Add("(RB-1004) Your request is unable to be processed at the moment.\nPlease try again later");
                                        }
                                    }else{
                                        result.Add(0);
                                        result.Add("(RB-1003) Your request is unable to be processed at the moment.\nPlease try again later");
                                    }
                                }
                            }else{
                                //If failed, Rollback
                                string qryRollBack = "UPDATE " + dbName + ".dbo.tbl_Request SET " +
                                    "tbl_Request.StatusCode = '200' WHERE " +
                                    "(tbl_Request.CLNum = @CLNum)";
                                Dictionary<string, object> dctRollBack = new Dictionary<string, object>();
                                dctRollBack.Add("@CLNum", IDCL);
                                
                                if (ExecQuery(qryRollBack, dctRollBack) > 0) {
                                    result.Add(0);
                                    result.Add("(RB-1002) Your request is unable to be processed at the moment.\nPlease try again later");
                                }else{
                                    result.Add(0);
                                    result.Add("(RB-1001) Your request is unable to be processed at the moment.\nPlease try again later");   
                                }
                            }
                        }else{
                            //Send Error Message on Failed on First
                            result.Add(0);
                            result.Add("(FOP-010) Your request is unable to be processed at the moment.\nPlease try again later");
                        }
                    }else{ //Else There is one or more Approval Steps

                        string QryMain = "UPDATE " + dbName + ".dbo.tbl_Request SET " + 
                            "tbl_Request.ApprovalCurrent = @ApprovalCurrent, " + 
                            "tbl_Request.ApprovalNext = @ApprovalNext, " + 
                            "tbl_Request.ApprovalStep = @ApprovalStep, " + 
                            "tbl_Request.ModifiedBy = @ModifiedBy, " +
                            "tbl_Request.ModifiedDate = GetDate() WHERE " +
                            "(tbl_Request.CLNum = @CLNum)";
                        Dictionary<string, object> dctQryMain = new Dictionary<string, object>();
                        dctQryMain.Add("@ApprovalCurrent", ApvNextID);
                        dctQryMain.Add("@ApprovalNext", NextPersonID);
                        dctQryMain.Add("@ApprovalStep", StepSeqNext);
                        dctQryMain.Add("@ModifiedBy", ApvCurrentID);
                        dctQryMain.Add("@CLNum", IDCL);
                        
                        if (ExecQuery(QryMain, dctQryMain) > 0) {

                            string QrySub = "UPDATE " + dbName + ".dbo.tbl_RequestApproval SET " +
                                "tbl_RequestApproval.Notes = @Notes, " +
                                "tbl_RequestApproval.StatusCode = '201' WHERE " +
                                "(tbl_RequestApproval.CLNum = @CLNum) AND " +
                                "(tbl_RequestApproval.StepSeq = @StepSeq)";
                            Dictionary<string, object> dctQrySub = new Dictionary<string, object>();
                            dctQrySub.Add("@Notes", ApvNotes);
                            dctQrySub.Add("@CLNum", IDCL);
                            dctQrySub.Add("@StepSeq", StepSeq);
                            
                            if (ExecQuery(QrySub, dctQrySub) > 0) { 
                                //Send Email: Request Mode
                                dctEmail.Add("MailTo", ApvNextEmail);
                                dctEmail.Add("ApvName", ApvNextName);

                                Dictionary<string,object> EmailStatus = SendEmail(dctEmail, EmailMode.REQUEST, EmailEngine.NETMAIL);
                                int EmailSentStatus = Convert.ToInt32(EmailStatus["Status"].ToString());
                                if (EmailSentStatus > 0){ 
                                    //TODO: Update DB Details. Email Sent = 1

                                    //Send Message Success
                                    result.Add(1);
                                    result.Add("Thank you.\nYour approval has been successfully updated");
                                }else{
                                    //If Failed RollBack All DBs
                                    string qryRollBack = "UPDATE " + dbName + ".dbo.tbl_Request SET " +
                                        "tbl_Request.ApprovalCurrent = @ApprovalCurrent, " +
                                        "tbl_Request.ApprovalNext = @ApprovalNext, " +
                                        "tbl_Request.ApprovalStep = @ApprovalStep WHERE " +
                                        "(tbl_Request.CLNum = @CLNum)";
                                    Dictionary<string, object> dctRollBack = new Dictionary<string, object>();
                                    dctRollBack.Add("@ApprovalCurrent", ApvCurrentID);
                                    dctRollBack.Add("@ApprovalNext", ApvNextID);
                                    dctRollBack.Add("@ApprovalStep", StepSeq);
                                    dctRollBack.Add("@CLNum", IDCL);

                                    if (ExecQuery(qryRollBack, dctRollBack) > 0){
                                        string qryRollBackExt = "UPDATE " + dbName + ".dbo.tbl_RequestApproval SET" +
                                            "tbl_RequestApproval.StatusCode = '200' WHERE " +
                                            "(tbl_RequestApproval.CLNum = @CLNum) AND " +
                                            "(tbl_RequestApproval.StepSeq = @StepSeq)";
                                        Dictionary<string, object> dctRollBackExt = new Dictionary<string, object>();
                                        dctRollBackExt.Add("@CLNum", IDCL);
                                        dctRollBackExt.Add("@StepSeq", StepSeq);

                                        if (ExecQuery(qryRollBackExt, dctRollBackExt) > 0){
                                            //Send Message Failure to user
                                            result.Add(0);
                                            result.Add(EmailStatus["Message"].ToString());
                                        }else{
                                            result.Add(0);
                                            result.Add("(RB-1102) Your request is unable to be processed at the moment.\nPlease try again later");
                                        }
                                    }else{
                                        result.Add(0);
                                        result.Add("(RB-1101) Your request is unable to be processed at the moment.\nPlease try again later");
                                    }
                                }
                            }else{
                                //TODO: RollBack DB Main with original data (Approval Current & Approval Next & Approval Step)

                                //Send Error Message: Failed on Second Process
                                result.Add(0);
                                result.Add("(FOP-021) Your request is unable to be processed at the moment.\nPlease try again later");
                            }
                        }else{
                            //Send Error Message: Failed on First Process
                            result.Add(0);
                            result.Add("(FOP-020) Your request is unable to be processed at the moment.\nPlease try again later");
                        }

                    }

                }else if (ApvStatus == "2") { // Status: Return for Information

                    string QryMain = "UPDATE " + dbName + ".dbo.tbl_Request SET " + 
                        "tbl_Request.ModifiedBy = @ModifiedBy, " + 
                        "tbl_Request.ModifiedDate = GetDate(), " + 
                        "tbl_Request.StatusCode = '203' WHERE " +
                        "(tbl_Request.CLNum = @CLNum)";
                    Dictionary<string, object> dctQryMain = new Dictionary<string, object>();
                    dctQryMain.Add("@ModifiedBy", ApvCurrentID);
                    dctQryMain.Add("@CLNum", IDCL);
                    
                    if (ExecQuery(QryMain, dctQryMain) > 0) {
                        string QrySub = "UPDATE " + dbName + ".dbo.tbl_RequestApproval SET " +
                            "tbl_RequestApproval.Notes = @Notes, " +
                            "tbl_RequestApproval.StatusCode = '203' WHERE " +
                            "(tbl_RequestApproval.CLNum = @CLNum) AND " +
                            "(tbl_RequestApproval.StepSeq = @StepSeq)";
                        Dictionary<string, object> dctQrySub = new Dictionary<string, object>();
                        dctQrySub.Add("@Notes", ApvNotes);
                        dctQrySub.Add("@CLNum", IDCL);
                        dctQrySub.Add("@StepSeq", StepSeq);

                        if (ExecQuery(QrySub, dctQrySub) > 0) {
                            //Send Email: Mode Return for Information
                            dctEmail["MailTo"] = RequesterEmail;
                            dctEmail["ApvName"] = "";

                            Dictionary<string, object> EmailStatus = SendEmail(dctEmail, EmailMode.RETURNED, EmailEngine.NETMAIL);
                            if ((int)EmailStatus["Status"] > 0){
                                //TODO: Update DB Details. Email Sent = 1

                                //Send Message Success
                                result.Add(1);
                                result.Add("Thank you.\nYour approval has been successfully updated");
                            }
                        }
                    }else{
                        //RollBack DB
                        string QryRollBack = "UPDATE " + dbName + ".dbo.tbl_Request SET " +
                            "tbl_Request.StatusCode = '200' WHERE " +
                            "(tbl_Request.CLNum = @CLNum)";
                        Dictionary<string, object> dctRollBack = new Dictionary<string, object>();
                        dctRollBack.Add("@CLNum", IDCL);
                        if (ExecQuery(QryRollBack, dctRollBack) > 0) {
                            //Send Error Message: Failed on Second Process
                            result.Add(0);
                            result.Add("(FOP-031) Your request is unable to be processed at the moment.\nPlease try again later");
                        }else{
                            //Send Error Message: Failed on First Process
                            result.Add(0);
                            result.Add("(FOP-030) Your request is unable to be processed at the moment.\nPlease try again later");
                        }
                    }

                }else if(ApvStatus == "3"){ //Status: Rejected

                    string QryMain = "UPDATE " + dbName + ".dbo.tbl_Request SET " +
                        "tbl_Request.ModifiedBy = @ModifiedBy, " +
                        "tbl_Request.ModifiedDate = GetDate(), " +
                        "tbl_Request.StatusCode = '204' WHERE " +
                        "(tbl_Request.CLNum = @CLNum)";
                    Dictionary<string, object> dctQryMain = new Dictionary<string, object>();
                    dctQryMain.Add("@ModifiedBy", ApvCurrentID);
                    dctQryMain.Add("@CLNum", IDCL);

                    if (ExecQuery(QryMain, dctQryMain) > 0){
                        string QrySub = "UPDATE " + dbName + ".dbo.tbl_RequestApproval SET " +
                            "tbl_RequestApproval.Notes = @Notes, " +
                            "tbl_RequestApproval.StatusCode = '203' WHERE " +
                            "(tbl_RequestApproval.CLNum = @CLNum) AND " +
                            "(tbl_RequestApproval.StepSeq = @StepSeq)";
                        Dictionary<string, object> dctQrySub = new Dictionary<string, object>();
                        dctQrySub.Add("@Notes", ApvNotes);
                        dctQrySub.Add("@CLNum", IDCL);
                        dctQrySub.Add("@StepSeq", StepSeq);
                        if (ExecQuery(QrySub, dctQrySub) > 0) {
                            //Send Email: Mode Return for Information
                            dctEmail["MailTo"] = RequesterEmail;
                            dctEmail["ApvName"] = "";

                            Dictionary<string, object> EmailStatus = SendEmail(dctEmail, EmailMode.REJECTED, EmailEngine.NETMAIL);
                            if ((int)EmailStatus["Status"] > 0){
                                //TODO: Update DB Details. Email Sent = 1

                                //Send Message Success
                                result.Add(1);
                                result.Add("Thank you.\nYour approval has been successfully updated");
                            }
                        }
                    }else{
                        //RollBack DB
                        string QryRollBack = "UPDATE " + dbName + ".dbo.tbl_Request SET " +
                            "tbl_Request.StatusCode = '200' WHERE " +
                            "(tbl_Request.CLNum = @CLNum)";
                        Dictionary<string, object> dctRollBack = new Dictionary<string, object>();
                        dctRollBack.Add("@CLNum", IDCL);
                        if (ExecQuery(QryRollBack, dctRollBack) > 0){
                            //Send Error Message: Failed on Second Process
                            result.Add(0);
                            result.Add("(FOP-041) Your request is unable to be processed at the moment.\nPlease try again later");
                        }else{
                            //Send Error Message: Failed on First Process
                            result.Add(0);
                            result.Add("(FOP-040) Your request is unable to be processed at the moment.\nPlease try again later");
                        }
                    }
                }
            }else{
                result.Add(0);
                result.Add("Your request is unable to be processed at the moment.\nPlease try again later");
            }
            //result = lstNextApp;
            if (result.Count() == 0)
            {
                result.Add(0);
                result.Add("0");
            }
            return result;
        }

        private static string GetApvNextID(string CLNum, int StepSeq, string ApvCurrent) {
            string result = "0";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_RequestApproval.NextStep " +
                "FROM " + dbName + ".dbo.tbl_RequestApproval " +
                "WHERE (tbl_RequestApproval.CLNum = @CLNum) " +
                "AND (tbl_RequestApproval.StepSeq = @StepSeq) " +
                "AND (tbl_RequestApproval.CurrentStep = @CurrentStep) " +
                "AND (tbl_RequestApproval.StatusCode = '200')";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();

                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", CLNum);
                    cmd.Parameters.AddWithValue("@StepSeq", StepSeq);
                    cmd.Parameters.AddWithValue("@CurrentStep", ApvCurrent);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows) {
                        if (rdr.Read()) {
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex) {
                    string errMsg = ex.Message;
                }finally {
                    if (cn.State == ConnectionState.Open) { 
                        cn.Close(); 
                    }
                }
            }
            return result;
        }

        private static string generatePageLink(string CLNum, string SSID){
            string result = "";

            Dictionary<string, object> dctSYS = Func.GetSysConfig();
            string SYSPort = "/";
            string SYSUrl = dctSYS["ServerAddress"].ToString() + SYSPort;
            string PageQry = "id=" + CLNum + "&atdn=" + SSID;
            
            result = SYSUrl + "Default.aspx?cl=" + CryptLib.Base64(PageQry, CryptMode.ENCRYPT);

            return result;
        }

        private static string generatePageLink(string CLNum, string SSID, string ExtraTxt){
            string result = "";
            Dictionary<string, object> dctSYS = Func.GetSysConfig();
            //string _Port = dctSYS["ServerPort"].ToString();
            //string SYSPort = (string.IsNullOrEmpty(_Port) || _Port == "80") ? "/" : ":" + _Port + "/";
            string SYSPort = "/";
            string SYSUrl = dctSYS["ServerAddress"].ToString() + SYSPort;
            string PageQry = "id=" + CLNum + "&atdn=" + SSID + ExtraTxt;
            string PageLink = SYSUrl + "Default.aspx?cl=" + CryptLib.Base64(PageQry, CryptMode.ENCRYPT);
            
            result = PageLink;
            return result;
        }

        private static List<object> CLData(string IDCL){
            List<object> result = new List<object>();

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT tbl_Request.ApprovalStep, tbl_Request.ApprovalCurrent, " +
                "tbl_Request.ApprovalNext, " +
                "(SELECT FullName FROM " + dbName + ".dbo.UserAccounts " + 
                "WHERE UserAccounts.UserName = tbl_Request.ApprovalNext) AS ApvName, " +
                "(SELECT Email FROM " + dbName + ".dbo.UserAccounts " +
                "WHERE UserAccounts.UserName = tbl_Request.ApprovalNext) AS ApvEmail, " +
                "tbl_Request.CreatedBy, ReqUser.FullName, ReqUser.Email, tbl_Request.Token, " +
                "tbl_Request.StatusCode FROM " + dbName + ".dbo.tbl_Request " +
                "INNER JOIN " + dbName + ".dbo.UserAccounts AS ReqUser ON ReqUser.UserName = tbl_Request.CreatedBy " +
                "WHERE (tbl_Request.CLNum = @CLNum)";
            
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn) {
                try {
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@CLNum", IDCL);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            int StepSeq = Convert.ToInt32(rdr.GetValue(0));
                            string ApvCurrent = rdr.GetValue(1).ToString();
                            string ApvNext = rdr.GetValue(2).ToString();
                            string ApvNextName = rdr.GetValue(3).ToString();
                            string ApvNextEmail = rdr.GetValue(4).ToString();
                            string Requester = rdr.GetValue(5).ToString();
                            string RequesterName = rdr.GetValue(6).ToString();
                            string RequesterEmail = rdr.GetValue(7).ToString();
                            string Token = rdr.GetValue(8).ToString();
                            string StatusCode = rdr.GetValue(9).ToString();

                            result.Add(true);
                            result.Add(StepSeq);
                            result.Add(ApvCurrent);
                            result.Add(ApvNext);
                            result.Add(ApvNextName);
                            result.Add(ApvNextEmail);
                            result.Add(Requester);
                            result.Add(RequesterName);
                            result.Add(RequesterEmail);
                            result.Add(Token);
                            result.Add(StatusCode);
                        }else{
                            result.Add(false);
                        }
                    }else{
                        result.Add(false);
                    }
                }catch(Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open) {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetEmailAddress(string UserID) {
            string result = "";

            string dbName = DBUtilities.GetDBName("devCon");
            string qry = "SELECT Email FROM " + dbName + ".dbo.UserAccounts WHERE " + 
                "(UserName = @UserName)";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    cmd.Parameters.AddWithValue("@UserName", UserID);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        if (rdr.Read()){
                            result = rdr.GetValue(0).ToString();
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static string GetApvStatus(string StatusCode) {
            string result = "";
            switch (StatusCode){
                case "1":
                    result = "201";
                    break;
                case "2":
                    result = "203";
                    break;
                case "3":
                    result = "204";
                    break;
            }
            return result;
        }
        
        private static Dictionary<string, object> GetEmailConfig(){
            Dictionary<string, object> result = new Dictionary<string, object>();
            string qry = "SELECT ConfigName, ConfigValue, Description FROM mstConfig WHERE " +
                    "ConfigGroup = 'Email'";
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(qry, cn);
                    SqlDataReader rdr = cmd.ExecuteReader();
                    if (rdr.HasRows){
                        while (rdr.Read()){
                            string CFGName = rdr.GetValue(0).ToString();
                            string CFGValue = rdr.GetValue(1).ToString();
                            string CFGDesc = rdr.GetValue(2).ToString();

                            result.Add(CFGName, CFGValue);
                        }
                    }
                }catch (Exception ex){
                    string errMsg = ex.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static Dictionary<string, object> SendEmail(Dictionary<string, string> EmailData, 
            EmailMode SendMode, EmailEngine EmailCodeBy){

            Dictionary<string, object> result = new Dictionary<string, object>();

            string EmailAddress = EmailData["MailTo"];
            string EmailSubject = EmailData["Subject"];
            if (!(string.IsNullOrEmpty(EmailAddress))){
                switch (EmailCodeBy){
                    case EmailEngine.CDOSYS:
                        //code for sending email via CDOSYS Mode
                        break;
                    case EmailEngine.NETMAIL:
                        Dictionary<string, object> dctConfig = GetEmailConfig();

                        MailMessage Mailer = new MailMessage();
                        Mailer.Subject = EmailData["Subject"];
                        Mailer.From = new MailAddress(dctConfig["FromEmail"].ToString(), dctConfig["FromName"].ToString());
                        Mailer.Priority = (MailPriority)Convert.ToInt32(dctConfig["Priority"]);
                        Mailer.IsBodyHtml = (bool)Convert.ToBoolean(dctConfig["IsBodyHTML"]);

                        Mailer.To.Add(EmailData["MailTo"]);
                        Mailer.CC.Add(EmailData["MailCC"]);

                        Dictionary<string, string> dctEmail = new Dictionary<string, string>();
                        dctEmail["Subject"] = EmailData["Subject"];
                        dctEmail["Requester"] = EmailData["RequesterName"];
                        dctEmail["AppvPerson"] = EmailData["ApvName"];
                        dctEmail["AppvPersonExt"] = "";
                        dctEmail["CLNum"] = EmailData["CLNum"];
                        dctEmail["Link"] = EmailData["Link"];

                        switch (SendMode) { 
                            case EmailMode.REQUEST:
                                //Mailer.CC.Add(EmailData["MailCC"]);
                                Mailer.Body = CLSMail.GenerateMessage(dctEmail, CLSMail.MessageMode.REQUEST);
                                break;
                            case EmailMode.APPROVED:
                                Mailer.Body = CLSMail.GenerateMessage(dctEmail, CLSMail.MessageMode.APPROVED);
                                break;
                            case EmailMode.RETURNED:
                                Mailer.Body = CLSMail.GenerateMessage(dctEmail, CLSMail.MessageMode.RETURNED);
                                break;
                            case EmailMode.REJECTED:
                                Mailer.Body = CLSMail.GenerateMessage(dctEmail, CLSMail.MessageMode.REJECTED);
                                break;
                        }

                        SmtpClient _SMTP = new SmtpClient();
                        _SMTP.DeliveryMethod = (SmtpDeliveryMethod)Convert.ToInt32(dctConfig["DeliveryMethod"]);
                        _SMTP.UseDefaultCredentials = (bool)Convert.ToBoolean(dctConfig["DefaultCredentials"]);

                        if (!(dctConfig["UserName"] == null && dctConfig["Password"] == null))
                        {
                            _SMTP.Credentials = new NetworkCredential(dctConfig["UserName"].ToString(), dctConfig["Password"].ToString());
                        }
                        _SMTP.Host = (string)dctConfig["Host"].ToString();
                        _SMTP.Port = (int)Convert.ToInt32(dctConfig["Port"]);
                        _SMTP.EnableSsl = (bool)Convert.ToBoolean(dctConfig["EnableSSL"]);
                        _SMTP.Timeout = (int)Convert.ToInt32(dctConfig["Timeout"]);

                        int Status = 0;
                        try{
                            _SMTP.Send(Mailer);
                            //Mailer.DeliveryNotificationOptions = DeliveryNotificationOptions.OnSuccess;

                            Status = 1;
                            result["Status"] = Status;
                            result["Message"] = "Sending Email Success!";
                        }catch (Exception ex){
                            string ErrMsg = ex.Message;
                            Status = 0;
                            result["Status"] = Status;
                            result["Message"] = ErrMsg;
                        }
                        break;
                }
            }else{
                result["Status"] = 0;
                result["Message"] = "Email address is empty!";
            }
            return result;
        }
        
        private static int ExecQuery(string query){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open)
                    {
                        cn.Close();
                    }
                }
            }
            return result;
        }

        private static int ExecQuery(string query, Dictionary<string, object> Params){
            int result = 0;
            SqlConnection cn = new SqlConnection(DBUtilities.strConnection("devCon"));
            using (cn){
                try{
                    cn.Open();
                    SqlCommand cmd = new SqlCommand(query, cn);
                    foreach (KeyValuePair<string, object> kvp in Params)
                    {
                        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
                    }
                    result = cmd.ExecuteNonQuery();
                }catch (Exception e){
                    string errMsg = e.Message;
                }finally{
                    if (cn.State == ConnectionState.Open){
                        cn.Close();
                    }
                }
            }
            return result;
        }

    }
}